#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : countcjdata.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/4/16 9:59                 #
#                                                                   #
#                   @Last Update  : 2021/4/16 9:59                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes: 分国家统计采集数据量及入加工库情况                                                         #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime

import pymysql

from PythonWebTools.settings import CJMYSQL_CONNECT
from countdata.models import cjdata


class Countcjsqldata:
    def __init__(self):
        self.table = ["`lawtext`", "`lawcasetext`", "`treaty`", "`lawnews`"]
        self.cjconn = pymysql.connect(host=CJMYSQL_CONNECT['MYSQL_HOST'], port=CJMYSQL_CONNECT['MYSQL_PORT'],
                                      db=CJMYSQL_CONNECT['MYSQL_DB'], user=CJMYSQL_CONNECT['MYSQL_USER'],
                                      passwd=CJMYSQL_CONNECT['MYSQL_PASSWD'], charset='utf8')

    def __del__(self):
        self.cjconn.close()

    def __reConnect(self):
        """
        查看数据库连接是否有效
        :return:
        """
        try:
            self.cjconn.ping()
        except:
            self.cjconn()

    def __countsql(self, tablename):
        """
        分国家统计采集数据库中的数据数量
        :param tablename: 统计的表名
        :return: resultdict: 分国家的采集数量和入库数量字典
        """
        resultdict = {}  # 分国家的采集数量和入库数量字典
        # 查询数据库表中的国家名
        sql = '''select DISTINCT SortA from %s ORDER BY SortA''' % tablename
        try:
            with self.cjconn.cursor() as cursor:
                cursor.execute(sql)
                countryS = cursor.fetchall()
        except Exception as e:
            print("查询%s表的国家数量异常:" % tablename + str(e))
            return resultdict
        # 分国家统计采集和入库数据量
        for country in countryS:
            country = country[0]
            # 查询采集数据量
            sql = '''SELECT COUNT(*) FROM %s WHERE SortA = "%s" and SYS_FLD_MARKSTATE != "2"''' % (tablename, country)
            print("查询%s表的%s国的已采集数据量" % (tablename, country))
            try:
                self.__reConnect()  # 数据库连接是否断开
                with self.cjconn.cursor() as cursor:
                    cursor.execute(sql)
                    counts = cursor.fetchall()
                resultdict[country] = [counts[0][0]]    # 采集数据量
            except Exception as e:
                resultdict[country] = ["-1"]
                print("查询%s表%s国家数量异常：" % (tablename, country) + str(e))
            # 查询入库数据量（新闻除外）
            if tablename == "`lawnews`":
                resultdict[country].append("-1")
                continue
            sql = '''SELECT COUNT(*) FROM %s WHERE SortA = "%s" AND SYS_FLD_MARKSTATE = "1"''' % (
                tablename, country)
            print("查询%s表的%s国的已入库数据量" % (tablename, country))
            try:
                self.__reConnect()  # 数据库连接是否断开
                with self.cjconn.cursor() as cursor:
                    cursor.execute(sql)
                    counts = cursor.fetchall()
                resultdict[country].append(counts[0][0])    # 入库数据量
            except Exception as e:
                resultdict[country].append("-1")
                print("查询%s表%s国家数量异常：" % (tablename, country) + str(e))
        return resultdict

    def __writein(self, datadict:dict, table, date):
        """
        将统计结果存入数据库中
        :param datadict: 统计结果字典
        :param table: 统计的表
        :param date: 统计日期
        :return:
        """
        for country in datadict.keys():     # 分国家依次写入数据
            data = datadict[country]
            test2 = cjdata(table=table, country=country, website="", data=data[0], inbound_data=data[1],
                           count_date=date)
            test2.save()


    def countvalue(self):
        """
        统计数据函数
        :return:
        """
        date_now = datetime.date.today()    # 统计日期
        for table in self.table:
            table_dict = {}
            table_dict = self.__countsql(table)
            self.__writein(table_dict, table, date_now)
